import _thread
import mysql.connector

import socket
import sys


mydb_ly = mysql.connector.connect(
    host="39.103.180.180",  # 数据库主机地址
    user="root",  # 数据库用户名
    port=3307,
    database="study",  # 数据库名称 没有的话插入操作报错
    passwd="root",  # 数据库密码
    auth_plugin="mysql_native_password"
    # host="192.168.47.102",  # 数据库主机地址
    # port=3306,
    # user="ccmadmin",  # 数据库用户名
    # database="mh_xiyou",#数据库名称 没有的话插入操作报错
    # passwd="211314wccA@"  # 数据库密码
)
mydb_wcc = mysql.connector.connect(
    host="39.103.180.180",  # 数据库主机地址
    user="root",  # 数据库用户名
    port=3307,
    database="study",  # 数据库名称 没有的话插入操作报错
    passwd="root",  # 数据库密码
    auth_plugin="mysql_native_password"
    # host="192.168.47.102",  # 数据库主机地址
    # port=3306,
    # user="ccmadmin",  # 数据库用户名
    # database="mh_xiyou",#数据库名称 没有的话插入操作报错
    # passwd="211314wccA@"  # 数据库密码
)
select_sql="SELECT id,msg FROM msg  where user_name = {} and is_send = 0"
update_sql="UPDATE msg SET is_send =1 WHERE id ={}"
insert_sql="INSERT INTO msg (msg,user_name,is_send) VALUES ({}, {},0)"
mycursor_ly = mydb_ly.cursor()
mycursor_wcc = mydb_wcc.cursor()

# 创建 socket 对象
server_socket_ly = socket.socket(
    socket.AF_INET, socket.SOCK_STREAM)
server_socket_wcc = socket.socket(
    socket.AF_INET, socket.SOCK_STREAM)
# 获取本地主机名
host = '39.103.180.180'

port_ly = 9091
port_wcc = 9090
# 绑定端口号
server_socket_wcc.bind((host, port_wcc))
server_socket_wcc.listen(5)

server_socket_ly.bind((host, port_ly))
server_socket_ly.listen(5)

# 设置最大连接数，超过后排队
wcc_client_socket, wcc_addr = server_socket_wcc.accept()
ly_client_socket, ly_addr = server_socket_ly.accept()





# 为线程定义一个函数
def re_send( user_name):
    mydb = mysql.connector.connect(
        host="39.103.180.180",  # 数据库主机地址
        user="root",  # 数据库用户名
        port=3307,
        database="study",  # 数据库名称 没有的话插入操作报错
        passwd="root",  # 数据库密码
        auth_plugin="mysql_native_password"
        # host="192.168.47.102",  # 数据库主机地址
        # port=3306,
        # user="ccmadmin",  # 数据库用户名
        # database="mh_xiyou",#数据库名称 没有的话插入操作报错
        # passwd="211314wccA@"  # 数据库密码
    )
    mycursor = mydb.cursor()
    tmp_sql = select_sql.format(user_name)
    #print("执行查询sql")
    #print(tmp_sql)
    mycursor.execute(tmp_sql)
    results = mycursor.fetchall()
    return_msg=""
    #print(len(results))
    for row in results:
       tmp_update_sql= update_sql.format(row[0])
       mycursor.execute(tmp_update_sql)
       mydb.commit()
       if return_msg:
           return_msg = return_msg + "\n" + return_msg + row[1]
       else:
           return_msg = return_msg + row[1]

    #print(return_msg)
    return return_msg


def send_msg_to():
    print("启动发送器!")
    while True:
        ly_rrr=re_send("'wcc'")
        if ly_rrr:
            print(ly_rrr)
            ly_client_socket.send(ly_rrr.encode('utf-8'))
        wcc_rrr=re_send("'ly'")
        if wcc_rrr:
            print(wcc_rrr)
            wcc_client_socket.send(wcc_rrr.encode('utf-8'))

def wcc_accept():
    print("启动wcc接收器")
    while True:
        wcc_gx = wcc_client_socket.recv(1024)
        wcc_tmp_insert_sql=insert_sql.format('"'+wcc_gx.decode('utf-8')+'"',"'wcc'")
        print(wcc_tmp_insert_sql)
        mycursor_wcc.execute(wcc_tmp_insert_sql)
        mydb_wcc.commit()
def ly_accept():
    print("启动ly接收器")
    while True:
        ly_gx = ly_client_socket.recv(1024)
        ly_tmp_insert_sql = insert_sql.format('"'+ly_gx.decode('utf-8')+'"', "'ly'")
        print(ly_tmp_insert_sql)
        mycursor_ly.execute(ly_tmp_insert_sql)
        mydb_ly.commit()


try:
   _thread.start_new_thread( send_msg_to,() )
   print("创建wcc接收器线程")
   _thread.start_new_thread( wcc_accept,())
   print("创建ly接收器线程")
   _thread.start_new_thread( ly_accept,())
except:
   print ("Error: 无法启动线程")

while 1:
   pass